C# 实现与PostgreSQL数据库交互（基于Npgsql）

/* ---------------------------------------------------------- * 文件名称：NpgsqlHandler.cs * * 开发环境： * Visual Studio V2015 * .NET Framework 4.5.2 * PostgreSQL 9.5.3 * * 版本历史： * V1.0 2016年06月22日 * 基于Npgsql实现与PostgreSQL数据库交互 ------------------------------------------------------------ */using Npgsql;namespace Com.FirstSolver{ public class NpgsqlHandler { /// <summary> /// 连接字符串 /// </summary> private string ConnectionString; /// <summary> /// 构造函数 /// </summary> public NpgsqlHandler() { // 生成数据库连接字符串 ConnectionString = new NpgsqlConnectionStringBuilder() { Database = "postgres", Host = "localhost", Port = 5432, Username = "postgres", Password = "wantgirl" }.ToString(); // 创建产品信息表 using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); using (NpgsqlCommand cmd = new NpgsqlCommand()) { cmd.CommandText = "CREATE TABLE IF NOT EXISTS Products(ProductID SERIAL PRIMARY KEY, Name TEXT, Description TEXT, Price MONEY, Category TEXT)"; cmd.Connection = conn; cmd.ExecuteNonQuery(); } } } /// <summary> /// 删除产品 /// </summary> /// <param name="id">产品编号</param> /// <returns> /// true：成功 /// false：失败 /// </returns> public bool Delete(int id) { using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); using (NpgsqlCommand cmd = new NpgsqlCommand()) { // 更新键值 cmd.CommandText = string.Format("DELETE FROM Products WHERE ProductID={0}", id); cmd.Connection = conn; return cmd.ExecuteNonQuery() == 1; } } } /// <summary> /// 新增产品 /// </summary> /// <param name="name">产品名称</param> /// <param name="description">产品描述</param> /// <param name="price">产品价格</param> /// <param name="category">产品分类</param> /// <returns> /// true：成功 /// false：失败 /// </returns> public bool Insert(string name, string description, decimal price, string category) { using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); using (NpgsqlCommand cmd = new NpgsqlCommand()) { // 更新键值 cmd.CommandText = string.Format("INSERT INTO Products(Name, Description, Price, Category) VALUES('{0}', '{1}', {2}, '{3}')", name, description, price, category); cmd.Connection = conn; return cmd.ExecuteNonQuery() == 1; } } } /// <summary> /// 产品列表 /// </summary> public System.Collections.IEnumerable Products { get { using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); using (NpgsqlCommand cmd = new NpgsqlCommand()) { cmd.CommandText = "SELECT * FROM Products"; cmd.Connection = conn; using (NpgsqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { yield return new Product(dr.GetInt32(0), dr.GetString(1), dr.GetString(2), dr.GetDecimal(3), dr.GetString(4)); } yield break; } } } } set { using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); using (NpgsqlCommand cmd = new NpgsqlCommand()) { cmd.Connection = conn; foreach (Product item in value) { if (item.ProductID == 0) { // 新增 cmd.CommandText = string.Format("INSERT INTO Products(Name, Description, Price, Category) VALUES('{0}', '{1}', {2}, '{3}')", item.Name, item.Description, item.Price, item.Category); } else { // 更新 cmd.CommandText = string.Format("UPDATE Products SET Name='{0}', Description='{1}', Price={2}, Category='{3}' WHERE ProductID={4}", item.Name, item.Description, item.Price, item.Category, item.ProductID); } cmd.ExecuteNonQuery(); } } } } } }}